import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


public class DruidDemo {
    /*
     * 查询所有
     * 若查询带条件的：select * from tb_brand where brandName = ?;
     * 设置参数：preSql.setString(1,"小米");
     * */
    @Test
    public void testSelectAll() throws Exception {
        // 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        // 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        // 获取数据库连接对象
        Connection con = dataSource.getConnection();
        // 判断数据库是否连接成功
        if (con == null) {
            System.out.println("数据库连接失败！");
            return;
        }
        // 预处理语句对象
        PreparedStatement preSql;
        // 结果集对象
        ResultSet rs;
        // 查询语句
        String sqlStr = "select * from tb_brand;";

        try {
            // 将sql语句转化为预处理对象
            preSql = con.prepareStatement(sqlStr);
            // 执行sql语句
            rs = preSql.executeQuery();
            // 分装Brand对象
            Brand brand = null;
            // 装载List集合
            List<Brand> brands = new ArrayList<>();
            // 输出结果集
            while (rs.next()) {
                // 获取数据
                int id = rs.getInt("id");
                String brandName = rs.getString("brand_name");
                String companyName = rs.getString("company_name");
                int orderd = rs.getInt("orderd");
                String description = rs.getString("description");
                int status = rs.getInt("status");

                // 封装Brand对象
                brand = new Brand();
                brand.setId(id);
                brand.setBrand_name(brandName);
                brand.setCompany_name(companyName);
                brand.setOrderd(orderd);
                brand.setDescription(description);
                brand.setStatus(status);

                // 装载集合
                brands.add(brand);
            }
            System.out.println(brands);

            // 释放资源
            rs.close();
            preSql.close();
            con.close();
        } catch (SQLException e) {
            System.out.println("出错！");
        }
    }

    /*
     * 插入
     * */
    @Test
    public void testInsert() throws Exception {
        // 模拟接收前端传过来的值
        String brandName = "哇哈哈1";
        String companyName = "哇哈哈股份有限公司";
        int orderd = 1;
        String description = "麻麻，我要喝哇哈哈，喝！喝一排！";
        int status = 0;

        // 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        // 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        // 获取数据库连接对象
        Connection con = dataSource.getConnection();
        // 判断是否连接成功
        if (con == null) {
            System.out.println("数据库连接失败！");
            return;
        }
        // 预处理语句对象
        PreparedStatement preSql;
        // 查询语句
        String sql = "insert into tb_brand(brand_name,company_name,orderd,description,status) values(?,?,?,?,?);";

        try {
            // 将sql语句转化为预处理对象
            preSql = con.prepareStatement(sql);
            // 设置参数
            preSql.setString(1, brandName);
            preSql.setString(2, companyName);
            preSql.setInt(3, orderd);
            preSql.setString(4, description);
            preSql.setInt(5, status);
            // 执行sql语句
            int count = preSql.executeUpdate();
            // 判断数据是否插入成功
            if (count > 0) {
                System.out.println("插入成功！");
                // 释放资源
                preSql.close();
                con.close();
            } else {
                System.out.println("插入失败！");
            }
        } catch (SQLException e) {
            System.out.println("出错！");
        }
    }

    /*
     * 删除
     * */
    @Test
    public void testDelete() throws Exception {
        // 模拟接收前端传递的id
        int id = 5;

        // 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        // 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        // 获取数据库连接对象
        Connection con = dataSource.getConnection();
        // 判断是否连接成功
        if (con == null) {
            System.out.println("数据库连接失败！");
            return;
        }
        // 预处理语句对象
        PreparedStatement preSql;
        // 删除语句
        String sql = "delete from tb_brand where id = ?;";

        try {
            // 将sql语句转化为预处理对象语句
            preSql = con.prepareStatement(sql);
            // 设置参数
            preSql.setInt(1, id);
            // 执行语句
            int count = preSql.executeUpdate();
            if (count > 0) {
                System.out.println("删除成功！");
                // 释放资源
                preSql.close();
                con.close();
            } else {
                System.out.println("删除失败！");
            }
        } catch (SQLException e) {
            System.out.println("出错");
        }
    }

    /*
     * 修改
     * */
    @Test
    public void testUpdate() throws Exception {
        // 模拟接收前端传过来的值
        int id = 2;
        String brandName = "华为";
        String companyName = "华为技术有限公司";
        int orderd = 100;
        String description = "华为致力于把数字带入千家万户！";
        int status = 0;

        // 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        // 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        // 获取数据库连接对象
        Connection con = dataSource.getConnection();
        // 判断是否连接成功
        if (con == null) {
            System.out.println("数据库连接失败！");
            return;
        }
        // 预处理语句对象
        PreparedStatement preSql;
        // 修改语句
        String sql =
                "update tb_brand\n" +
                        "set brand_name = ?,company_name = ?,orderd = ?,description = ?,`status` = ?\n" +
                        "where id = ?;";
        try {
            // 将sql语句转化为与处理对象
            preSql = con.prepareStatement(sql);
            // 设置参数
            preSql.setString(1, brandName);
            preSql.setString(2, companyName);
            preSql.setInt(3, orderd);
            preSql.setString(4, description);
            preSql.setInt(5, status);
            preSql.setInt(6, id);
            // 执行sql
            int count = preSql.executeUpdate();
            if (count > 0) {
                System.out.println("修改成功！");
                // 释放资源
                preSql.close();
                con.close();
            } else {
                System.out.println("修改失败！");
            }
        } catch (SQLException e) {
            System.out.println("出错");
        }
    }
}